package health_system.health_system.repository;

import health_system.health_system.entity.Moment;
import health_system.health_system.entity.MomentMedia;
import health_system.health_system.entity.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface MomentMediaRepository extends JpaRepository<MomentMedia, Long> {
    
    // Find all media for a moment
    List<MomentMedia> findByMomentOrderByOrderIndex(Moment moment);
    
    // Find all media by a user
    @Query("SELECT mm FROM MomentMedia mm WHERE mm.moment.user = :user ORDER BY mm.createdAt DESC")
    List<MomentMedia> findByUserOrderByCreatedAtDesc(@Param("user") User user);
    
    // Find with pagination
    @Query("SELECT mm FROM MomentMedia mm WHERE mm.moment.user = :user ORDER BY mm.createdAt DESC")
    Page<MomentMedia> findByUserOrderByCreatedAtDesc(@Param("user") User user, Pageable pageable);
    
    // Find by media type
    @Query("SELECT mm FROM MomentMedia mm WHERE mm.moment = :moment AND mm.mediaType = :mediaType ORDER BY mm.orderIndex")
    List<MomentMedia> findByMomentAndMediaType(
            @Param("moment") Moment moment,
            @Param("mediaType") MomentMedia.MediaType mediaType);
    
    // Count media by type for a moment
    @Query("SELECT COUNT(mm) FROM MomentMedia mm WHERE mm.moment = :moment AND mm.mediaType = :mediaType")
    Long countByMomentAndMediaType(
            @Param("moment") Moment moment, 
            @Param("mediaType") MomentMedia.MediaType mediaType);
    
    // Find all images in moments visible to a user
    @Query("SELECT mm FROM MomentMedia mm WHERE mm.mediaType = 'IMAGE' AND " +
           "(" +
           "(mm.moment.visibility = 'PUBLIC') OR " +
           "(mm.moment.user = :viewer) OR " +
           "(mm.moment.visibility = 'FRIENDS' AND " +
           "EXISTS (SELECT f FROM Friendship f WHERE " +
           "((f.requester = mm.moment.user AND f.addressee = :viewer) OR " +
           "(f.requester = :viewer AND f.addressee = mm.moment.user)) AND f.status = 'ACCEPTED')) OR " +
           "(mm.moment.visibility = 'CUSTOM' AND :viewer MEMBER OF mm.moment.visibleToUsers)" +
           ") " +
           "ORDER BY mm.createdAt DESC")
    List<MomentMedia> findImagesVisibleToUser(@Param("viewer") User viewer, Pageable pageable);
    
    // Find all videos in moments visible to a user
    @Query("SELECT mm FROM MomentMedia mm WHERE mm.mediaType = 'VIDEO' AND " +
           "(" +
           "(mm.moment.visibility = 'PUBLIC') OR " +
           "(mm.moment.user = :viewer) OR " +
           "(mm.moment.visibility = 'FRIENDS' AND " +
           "EXISTS (SELECT f FROM Friendship f WHERE " +
           "((f.requester = mm.moment.user AND f.addressee = :viewer) OR " +
           "(f.requester = :viewer AND f.addressee = mm.moment.user)) AND f.status = 'ACCEPTED')) OR " +
           "(mm.moment.visibility = 'CUSTOM' AND :viewer MEMBER OF mm.moment.visibleToUsers)" +
           ") " +
           "ORDER BY mm.createdAt DESC")
    List<MomentMedia> findVideosVisibleToUser(@Param("viewer") User viewer, Pageable pageable);
    
    // Count media for a moment
    Long countByMoment(Moment moment);
    
    // Delete all media for a moment
    void deleteByMoment(Moment moment);
    
    // Find media by partial filename search
    @Query("SELECT mm FROM MomentMedia mm WHERE mm.moment.user = :user AND mm.mediaUrl LIKE %:filename% ORDER BY mm.createdAt DESC")
    List<MomentMedia> findByUserAndFilenameContaining(@Param("user") User user, @Param("filename") String filename);
    
    // Find moments with most media in user's visible moments
    @Query("SELECT m, COUNT(mm) FROM Moment m JOIN m.mediaList mm WHERE " +
           // Same visibility filters as in MomentRepository
           "(" +
           "(m.visibility = 'PUBLIC') OR " +
           "(m.user = :viewer) OR " +
           "(m.visibility = 'FRIENDS' AND " +
           "EXISTS (SELECT f FROM Friendship f WHERE " +
           "((f.requester = m.user AND f.addressee = :viewer) OR " +
           "(f.requester = :viewer AND f.addressee = m.user)) AND f.status = 'ACCEPTED')) OR " +
           "(m.visibility = 'CUSTOM' AND :viewer MEMBER OF m.visibleToUsers)" +
           ") " +
           "GROUP BY m ORDER BY COUNT(mm) DESC")
    List<Object[]> findMomentsWithMostMediaVisibleToUser(@Param("viewer") User viewer, Pageable pageable);
} 